using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Collections;


namespace LogAnalyzerIIS.LogClasses
{
	public class StatisticsWriter
	{
		StatisticsPool statisticsPool;

		/// <summary>
		/// stores cells addresses used for formulas
		/// </summary>
		private struct GlobalCells
		{
			/// <summary>
			/// Address of cell that contains total days value
			/// </summary>
			public string TotalDays;

			/// <summary>
			/// Address of cell that contains total hosts value
			/// </summary>
			public string TotalHosts;

			/// <summary>
			/// /// Address of cell that contains total hits value
			/// </summary>
			public string TotalHits;
		}

		// supported extensions list 

		/// <summary>
		/// extensions of resources that will be assumed as web pages
		/// </summary>
		public static List<string> PageExtensions = new List<string>(new string[] { "htm", "html", "asp", "cgi", "php", "php3", "php4", "phtml", "shtml", "xml", "aspx", "asmx" });

		/// <summary>
		/// extensions of resources that will be assumed as images
		/// </summary>
		public static List<string> ImageExtensions = new List<string>(new string[] { "gif", "jpg", "jpeg", "png", "bmp" });

		/// <summary>
		/// extensions of resources that will be assumed as files
		/// </summary>
		public static List<string> FileExtensions = new List<string>(new string[] { "zip", "rar", "exe", "gz", "tar", "txt", "ico", "pdf", "mp3", "mpg", "mpeg", "avi", "mov", "wmv", "rm", "asf", "mid", "swf" });

		private static List<Excel.Shape> Shapes = new List<Microsoft.Office.Interop.Excel.Shape>();

		// default chart size
		private int ChartWidth = 420;
		private int ChartHeight = 280;

		// addresses of cells used in formulas of the entire worksheet
		private GlobalCells CellAddresses = new GlobalCells();

		// used to count worksheet size for shrinking to A4 size
		private char MaxColumn = 'A';

		/// <summary>
		/// Initializes statistics writer
		/// </summary>
		/// <param name="sp">Statistics pool to get values for writing</param>
		public StatisticsWriter(StatisticsPool sp)
		{
			statisticsPool = sp;
			CellAddresses.TotalDays = "";
			CellAddresses.TotalHosts = "";
			CellAddresses.TotalHits = "";
		}

		/// <summary>
		/// Gets address of the Range object
		/// </summary>
		/// <param name="cell">Range object</param>
		/// <returns>String of range address</returns>
		private string GetRangeAddress(object cell)
		{
			return (cell as Excel.Range).get_Address(false, false, Excel.XlReferenceStyle.xlA1, false, Type.Missing);
		}

		/// <summary>
		/// Returns Excel binary formula string
		/// </summary>
		/// <param name="Operand1">First operand</param>
		/// <param name="Operand2">Second operand</param>
		/// <param name="Operator">Operator</param>
		/// <returns>String of built formula</returns>
		private string BuildFormula(string Operand1, string Operand2, string Operator)
		{
			return "=" + Operand1 + Operator + Operand2;
		}

		/// <summary>
		///   Prints general statistics into specified worksheet, returns true if report period isn't empty.
		/// </summary>
		/// <param name="ws">
		///   Worksheet to output statistics to.
		/// </param>
		/// <param name="row">
		///   Number of the row where output to be started.
		/// </param>
		private bool PrintGeneralStatistics(Excel.Worksheet excelWorksheet, ref int row)
		{
			excelWorksheet.Cells[row, 1] = excelWorksheet.Name;
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Size = Convert.ToDouble((excelWorksheet.Cells[row, 1] as Excel.Range).Font.Size) + 2;
			(excelWorksheet.Cells[row++, 1] as Excel.Range).Font.Bold = true;
			row++;

			excelWorksheet.Cells[row, 1] = "General Statistics";
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			(excelWorksheet.Cells[row++, 1] as Excel.Range).Font.Bold = true;
			row++;

			if (statisticsPool.TotalHitsNHosts.Hits <= 0)
			{
				excelWorksheet.Cells[row, 1] = "Logs for the selected reporting period have no data";
				return false;
			}

			excelWorksheet.Cells[row, 1] = "Start Date";
			if (statisticsPool.MinDate == DateTime.MaxValue)
				excelWorksheet.Cells[row++, 2] = "n/a";
			else
				excelWorksheet.Cells[row++, 2] = statisticsPool.MinDate;

			excelWorksheet.Cells[row, 1] = "End Date";
			if (statisticsPool.MaxDate == DateTime.MinValue)
				excelWorksheet.Cells[row++, 2] = "n/a";
			else
				excelWorksheet.Cells[row++, 2] = statisticsPool.MaxDate;

			excelWorksheet.Cells[row, 1] = "Total Days";
			CellAddresses.TotalDays = GetRangeAddress(excelWorksheet.Cells[row, 2]);

			excelWorksheet.Cells[row++, 2] = BuildFormula(GetRangeAddress(excelWorksheet.Cells[row - 2, 2]), GetRangeAddress(excelWorksheet.Cells[row - 3, 2]), "-") + "+1";

			row++;

			// hits block
			excelWorksheet.Cells[row, 1] = "Hits";
			(excelWorksheet.Cells[row++, 1] as Excel.Range).Font.Bold = true;
			excelWorksheet.Cells[row, 1] = "Total Hits:";
			CellAddresses.TotalHits = GetRangeAddress(excelWorksheet.Cells[row, 2]);
			excelWorksheet.Cells[row++, 2] = statisticsPool.TotalHitsNHosts.Hits;

			// count total number of days in the processing period
			int daycount = 1;
			DateTime tempDate = statisticsPool.MinDate;
			while (tempDate < statisticsPool.MaxDate)
			{
				tempDate = tempDate.AddDays(1);
				daycount++;
			}

			excelWorksheet.Cells[row, 1] = "Average Hits per Day:";
			excelWorksheet.Cells[row++, 2] = BuildFormula(CellAddresses.TotalHits, CellAddresses.TotalDays, "/");

			// save row number to put here formula below
			int rowHitsPerVisitor = row;
			excelWorksheet.Cells[row++, 1] = "Average Hits per Visitor:";

			row++;

			// visitors block
			excelWorksheet.Cells[row, 1] = "Visitors";
			(excelWorksheet.Cells[row++, 1] as Excel.Range).Font.Bold = true;
			excelWorksheet.Cells[row, 1] = "Total Visitors:";

			CellAddresses.TotalHosts = GetRangeAddress(excelWorksheet.Cells[row, 2]);

			if (statisticsPool.TotalHitsNHosts.Hosts > 0)
				excelWorksheet.Cells[rowHitsPerVisitor, 2] = BuildFormula(CellAddresses.TotalHits, CellAddresses.TotalHosts, "/");
			else
				excelWorksheet.Cells[rowHitsPerVisitor, 2] = "n/a";

			excelWorksheet.Cells[row++, 2] = statisticsPool.TotalHitsNHosts.Hosts;
			excelWorksheet.Cells[row, 1] = "Average Visitors per Day:";
			excelWorksheet.Cells[row++, 2] = BuildFormula(CellAddresses.TotalHosts, CellAddresses.TotalDays, "/");
			excelWorksheet.Cells[row, 1] = "Total Unique IPs:";
			excelWorksheet.Cells[row++, 2] = statisticsPool.UniqueIPs.Count;
			row++;

			// set current boundary column number
			if (MaxColumn < 'A' + 1)
				MaxColumn = (char)('A' + 1);

			return true;
		}

		/// <summary>
		///   Prints entry pages statistics into specified worksheet.
		/// </summary>
		/// <param name="ws">
		///   Worksheet to output statistics to.
		/// </param>
		/// <param name="row">
		///   Number of the row where output to be started.
		/// </param>
		private void PrintEntryPages(Excel.Worksheet excelWorksheet, ref int row)
		{
			excelWorksheet.Cells[row, 1] = "Entry Pages";
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			(excelWorksheet.Cells[row++, 1] as Excel.Range).Font.Bold = true;

			SetHeaderCellText(excelWorksheet.Cells[row, 1], "Rank");
			SetHeaderCellText(excelWorksheet.Cells[row, 2], "Page");
			SetHeaderCellText(excelWorksheet.Cells[row, 3], "Hosts");
			row++;

			int rank = 1;

			// here key is page URL, value is page host number
			foreach (KeyValuePair<string, int> kv in statisticsPool.EntryPages)
			{
				excelWorksheet.Cells[row, 1] = rank++;
				(excelWorksheet.Cells[row, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
				excelWorksheet.Cells[row, 2] = kv.Key;
				excelWorksheet.Cells[row++, 3] = kv.Value;
			}

			// sort entry pages by the host number
			excelWorksheet.get_Range(excelWorksheet.Cells[row - statisticsPool.EntryPages.Count, 2], excelWorksheet.Cells[row - 1, 3]).Sort(
					 excelWorksheet.Cells[row - statisticsPool.EntryPages.Count, 3], Excel.XlSortOrder.xlDescending,
					 Type.Missing, Type.Missing, Excel.XlSortOrder.xlDescending, Type.Missing,
					 Excel.XlSortOrder.xlDescending, Excel.XlYesNoGuess.xlGuess, Type.Missing,
					 Type.Missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlStroke,
					 Excel.XlSortDataOption.xlSortTextAsNumbers, Excel.XlSortDataOption.xlSortTextAsNumbers,
					 Excel.XlSortDataOption.xlSortTextAsNumbers);

			// set current boundary column number
			if (MaxColumn < 'A' + 2)
				MaxColumn = (char)('A' + 2);
		}

		/// <summary>
		///   Prints exit pages statistics into specified worksheet.
		/// </summary>
		/// <param name="ws">
		///   Worksheet to output statistics to.
		/// </param>
		/// <param name="row">
		///   Number of the row where output to be started.
		/// </param>
		private void PrintExitPages(Excel.Worksheet excelWorksheet, ref int row)
		{
			excelWorksheet.Cells[row, 1] = "Exit Pages";
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			(excelWorksheet.Cells[row++, 1] as Excel.Range).Font.Bold = true;

			SetHeaderCellText(excelWorksheet.Cells[row, 1], "Rank");
			SetHeaderCellText(excelWorksheet.Cells[row, 2], "Page");
			SetHeaderCellText(excelWorksheet.Cells[row, 3], "Hosts");
			row++;

			int rank = 1;

			// here key is page URL, value is page host number
			foreach (KeyValuePair<string, int> kv in statisticsPool.ExitPages)
			{
				excelWorksheet.Cells[row, 1] = rank++;
				(excelWorksheet.Cells[row, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
				excelWorksheet.Cells[row, 2] = kv.Key;
				excelWorksheet.Cells[row++, 3] = kv.Value;
			}

			// sort exit pages by the host number
			excelWorksheet.get_Range(excelWorksheet.Cells[row - statisticsPool.ExitPages.Count, 2], excelWorksheet.Cells[row - 1, 3]).Sort(
					 excelWorksheet.Cells[row - statisticsPool.ExitPages.Count, 3], Excel.XlSortOrder.xlDescending,
					 Type.Missing, Type.Missing, Excel.XlSortOrder.xlDescending, Type.Missing,
					 Excel.XlSortOrder.xlDescending, Excel.XlYesNoGuess.xlGuess, Type.Missing,
					 Type.Missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlStroke,
					 Excel.XlSortDataOption.xlSortTextAsNumbers, Excel.XlSortDataOption.xlSortTextAsNumbers,
					 Excel.XlSortDataOption.xlSortTextAsNumbers);


			// set current boundary column number
			if (MaxColumn < 'A' + 2)
				MaxColumn = (char)('A' + 2);
		}

		/// <summary>
		///   Prints browsers statistics into specified worksheet.
		/// </summary>
		/// <param name="ws">
		///   Worksheet to output statistics to.
		/// </param>
		/// <param name="row">
		///   Number of the row where output to be started.
		/// </param>
		public void PrintBrowsers(Excel.Worksheet excelWorksheet, ref int row)
		{
			// put section title
			excelWorksheet.Cells[row, 1] = "Browsers";
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			(excelWorksheet.Cells[row++, 1] as Excel.Range).Font.Bold = true;

			int chartrow = ++row;

			// get top position of the chart by the first empty cell
			double ChartTop = (double)(excelWorksheet.Cells[row, 1] as Excel.Range).Top;

			// skip number of rows in according to shape height
			int startrow = row = row + GetVertCellOffset(excelWorksheet, row, ChartHeight);

			// create table header
			SetHeaderCellText(excelWorksheet.Cells[row, 1], "Rank");
			SetHeaderCellText(excelWorksheet.Cells[row, 2], "Name");
			SetHeaderCellText(excelWorksheet.Cells[row, 3], "Hits");
			SetHeaderCellText(excelWorksheet.Cells[row, 5], "Hosts");
			row++;

			string s = "";

			// here key is browser name, value is HitsNHosts structure
			foreach (KeyValuePair<string, HitsNHosts> kv in statisticsPool.Browsers)
			{
				// replace empty or omitted browser names
				switch (kv.Key)
				{
					case "-":
						s = "n/a";
						break;
					case "":
						s = "Unknown";
						break;
					default:
						s = kv.Key;
						break;
				}

				excelWorksheet.Cells[row, 2] = s;
				excelWorksheet.Cells[row, 3] = kv.Value.Hits;
				excelWorksheet.Cells[row++, 5] = kv.Value.Hosts;
			}

			// sort browsers by the hits number
			excelWorksheet.get_Range(excelWorksheet.Cells[row - statisticsPool.Browsers.Count, 2], excelWorksheet.Cells[row - 1, 6]).Sort(
					excelWorksheet.Cells[row - statisticsPool.Browsers.Count, 3], Excel.XlSortOrder.xlDescending,
					Type.Missing, Type.Missing, Excel.XlSortOrder.xlDescending, Type.Missing,
					Excel.XlSortOrder.xlDescending, Excel.XlYesNoGuess.xlGuess, Type.Missing,
					Type.Missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlStroke,
					Excel.XlSortDataOption.xlSortTextAsNumbers, Excel.XlSortDataOption.xlSortTextAsNumbers,
					Excel.XlSortDataOption.xlSortTextAsNumbers);

			// add percentage formulas and set number format of the appropriate cells
			for (int i = 1; i <= statisticsPool.Browsers.Count; i++)
			{
				// put rank values
				excelWorksheet.Cells[startrow + i, 1] = i.ToString();
				(excelWorksheet.Cells[startrow + i, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

				// put percentage for hits values
				excelWorksheet.Cells[startrow + i, 4] = "=C" + (startrow + i).ToString() + "/SUM(C" + startrow.ToString() + ":C" + (startrow + statisticsPool.Browsers.Count).ToString() + ")";
				(excelWorksheet.Cells[startrow + i, 4] as Excel.Range).NumberFormat = "0.00%";

				// put percentage for hosts values
				excelWorksheet.Cells[startrow + i, 6] = "=E" + (startrow + i).ToString() + "/SUM(E" + startrow.ToString() + ":E" + (startrow + statisticsPool.Browsers.Count).ToString() + ")";
				(excelWorksheet.Cells[startrow + i, 6] as Excel.Range).NumberFormat = "0.00%";
			}

			#region Bulding a chart

			Excel.Shape shape = excelWorksheet.Shapes.AddChart(Excel.XlChartType.xl3DPie, 1, ChartTop, ChartWidth, ChartHeight);

			// get the range of source values for the chart (build by hosts)
			Excel.Range ChartSource = excelWorksheet.Application.Union(excelWorksheet.get_Range(excelWorksheet.Cells[startrow, 2], excelWorksheet.Cells[row - 1, 2]),
					excelWorksheet.get_Range(excelWorksheet.Cells[startrow, 5], excelWorksheet.Cells[row - 1, 5]), Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing);
			try
			{
				shape.Chart.SetSourceData(ChartSource, Excel.XlRowCol.xlColumns);
			}
			finally
			{
				shape.Chart.ChartTitle.Text = "Browsers";
				Shapes.Add(shape);
			}

			#endregion

			// set current boundary column number
			if (MaxColumn < 'A' + 5)
				MaxColumn = (char)('A' + 5);
		}

		/// <summary>
		///   Prints operating systems statistics into specified worksheet.
		/// </summary>
		/// <param name="ws">
		///   Worksheet to output statistics to.
		/// </param>
		/// <param name="row">
		///   Number of the row where output to be started.
		/// </param>
		public void PrintOperatingSystems(Excel.Worksheet excelWorksheet, ref int row)
		{
			excelWorksheet.Cells[row, 1] = "Operating Systems";
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			(excelWorksheet.Cells[row++, 1] as Excel.Range).Font.Bold = true;

			int chartrow = ++row;
			// get top position of the chart by the first empty cell
			double ChartTop = (double)(excelWorksheet.Cells[row, 1] as Excel.Range).Top;
			// skip number of rows in according to shape height
			int startrow = row = row + GetVertCellOffset(excelWorksheet, row, ChartHeight);

			SetHeaderCellText(excelWorksheet.Cells[row, 1], "Rank");
			SetHeaderCellText(excelWorksheet.Cells[row, 2], "Name");
			SetHeaderCellText(excelWorksheet.Cells[row, 3], "Hits");
			SetHeaderCellText(excelWorksheet.Cells[row, 5], "Hosts");
			row++;

			string s = "";
			// here key is OS identifier, value is HitsNHosts structure
			foreach (KeyValuePair<string, HitsNHosts> kv in statisticsPool.OSes)
			{
				// replace OS identifiers with OS names, process empty or omitted OS names 
				switch (kv.Key)
				{
					case "WindowsNT5.01":
						s = "Windows 2000 SP1";
						break;
					case "WindowsNT5.0":
						s = "Windows 2000";
						break;
					case "WindowsNT5.1":
						s = "Windows XP";
						break;
					case "WindowsNT5.2":
						s = "Windows 2003";
						break;
					case "WindowsNT6.0":
						s = "Windows Vista";
						break;
					case "-":
						s = "n/a";
						break;
					case "":
						s = "Unknown";
						break;
					default:
						s = kv.Key;
						break;
				}

				excelWorksheet.Cells[row, 2] = s;
				excelWorksheet.Cells[row, 3] = kv.Value.Hits;
				excelWorksheet.Cells[row++, 5] = kv.Value.Hosts;
			}

			// sort OSes by the hits number
			excelWorksheet.get_Range(excelWorksheet.Cells[row - statisticsPool.OSes.Count, 2], excelWorksheet.Cells[row - 1, 6]).Sort(
					excelWorksheet.Cells[row - statisticsPool.OSes.Count, 3], Microsoft.Office.Interop.Excel.XlSortOrder.xlDescending,
					Type.Missing, Type.Missing, Excel.XlSortOrder.xlDescending, Type.Missing,
					Excel.XlSortOrder.xlDescending, Excel.XlYesNoGuess.xlGuess, Type.Missing,
					Type.Missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlStroke,
					Excel.XlSortDataOption.xlSortTextAsNumbers, Excel.XlSortDataOption.xlSortTextAsNumbers,
					Excel.XlSortDataOption.xlSortTextAsNumbers);

			// add percentage formulas and set number format of the appropriate cells
			for (int i = 1; i <= statisticsPool.OSes.Count; i++)
			{
				// put rank values
				excelWorksheet.Cells[startrow + i, 1] = i.ToString();
				(excelWorksheet.Cells[startrow + i, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

				// put percentage for hits values
				excelWorksheet.Cells[startrow + i, 4] = "=C" + (startrow + i).ToString() + "/SUM(C" + startrow.ToString() + ":C" + (startrow + statisticsPool.OSes.Count).ToString() + ")";
				(excelWorksheet.Cells[startrow + i, 4] as Excel.Range).NumberFormat = "0.00%";

				// put percentage for hosts values
				excelWorksheet.Cells[startrow + i, 6] = "=E" + (startrow + i).ToString() + "/SUM(E" + startrow.ToString() + ":E" + (startrow + statisticsPool.OSes.Count).ToString() + ")";
				(excelWorksheet.Cells[startrow + i, 6] as Excel.Range).NumberFormat = "0.00%";
			}

			#region Bulding a chart


			Excel.Shape shape = excelWorksheet.Shapes.AddChart(Excel.XlChartType.xl3DPie, 1, ChartTop, ChartWidth, ChartHeight);
			// get the range of source values for the chart (build by hosts)

			Excel.Range ChartSource = excelWorksheet.Application.Union(excelWorksheet.get_Range(excelWorksheet.Cells[startrow, 2], excelWorksheet.Cells[row - 1, 2]),
					excelWorksheet.get_Range(excelWorksheet.Cells[startrow, 5], excelWorksheet.Cells[row - 1, 5]), Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing);

			try
			{
				shape.Chart.SetSourceData(ChartSource, Excel.XlRowCol.xlColumns);
			}
			finally
			{
				shape.Chart.ChartTitle.Text = "Operating systems";
				Shapes.Add(shape);
			}
			#endregion

			// set current boundary column number
			if (MaxColumn < 'A' + 5)
				MaxColumn = (char)('A' + 5);
		}

		/// <summary>
		///   Prints resources statistics into specified worksheet.
		/// </summary>
		/// <param name="ws">
		///   Worksheet to output statistics to.
		/// </param>
		/// <param name="row">
		///   Number of the row where output to be started.
		/// </param>
		public void PrintResources(Excel.Worksheet excelWorksheet, ref int row)
		{
			Dictionary<string, int> urls = statisticsPool.StatList[9] as Dictionary<string, int>;
			// count number of hits for each resource type
			int pages = 0;
			int files = 0;
			int images = 0;
			int others = 0;
			foreach (KeyValuePair<string, int> kv in urls)
			{
				string extension = StatisticsReader.GetExtensionByURL(kv.Key);
				// if resource is a page
				if (PageExtensions.IndexOf(extension) >= 0)
					pages += kv.Value;
				else
				{
					// if resource is a file
					if (FileExtensions.IndexOf(extension) >= 0)
						files += kv.Value;
					else
					{
						// if resource is an image
						if (ImageExtensions.IndexOf(extension) >= 0)
							images += kv.Value;
						else
							others += kv.Value;
					}
				}
			}

			string CellAddr = "";

			excelWorksheet.Cells[row, 1] = "Resources";
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			(excelWorksheet.Cells[row++, 1] as Excel.Range).Font.Bold = true;

			excelWorksheet.Cells[row, 1] = "Total Page Views:";
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			CellAddr = GetRangeAddress(excelWorksheet.Cells[row, 2]);
			excelWorksheet.Cells[row++, 2] = pages;

			excelWorksheet.Cells[row, 1] = "Average Page Views per Day:";
			excelWorksheet.Cells[row++, 2] = BuildFormula(CellAddr, CellAddresses.TotalDays, "/");

			excelWorksheet.Cells[row, 1] = "Average Page Views per Visitor:";
			if (statisticsPool.TotalHitsNHosts.Hosts > 0)
				excelWorksheet.Cells[row++, 2] = BuildFormula(CellAddr, CellAddresses.TotalHosts, "/");
			else
				excelWorksheet.Cells[row++, 2] = "n/a";

			excelWorksheet.Cells[row, 1] = "Total File Downloads:";
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			CellAddr = GetRangeAddress(excelWorksheet.Cells[row, 2]);
			excelWorksheet.Cells[row++, 2] = files;

			excelWorksheet.Cells[row, 1] = "Average File Downloads per Day:";
			excelWorksheet.Cells[row++, 2] = BuildFormula(CellAddr, CellAddresses.TotalDays, "/");

			excelWorksheet.Cells[row, 1] = "Average File Downloads per Visitor:";
			if (statisticsPool.TotalHitsNHosts.Hosts > 0)
				excelWorksheet.Cells[row++, 2] = BuildFormula(CellAddr, CellAddresses.TotalHosts, "/");
			else
				excelWorksheet.Cells[row++, 2] = "n/a";

			excelWorksheet.Cells[row, 1] = "Total Images:";
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			CellAddr = GetRangeAddress(excelWorksheet.Cells[row, 2]);
			excelWorksheet.Cells[row++, 2] = images;

			excelWorksheet.Cells[row, 1] = "Average Images per Day:";
			excelWorksheet.Cells[row++, 2] = BuildFormula(CellAddr, CellAddresses.TotalDays, "/"); ;

			excelWorksheet.Cells[row, 1] = "Average Images per Visitor:";
			if (statisticsPool.TotalHitsNHosts.Hosts > 0)
				excelWorksheet.Cells[row++, 2] = BuildFormula(CellAddr, CellAddresses.TotalHosts, "/");
			else
				excelWorksheet.Cells[row++, 2] = "n/a";

			excelWorksheet.Cells[row, 1] = "Total Other Resources:";
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			CellAddr = GetRangeAddress(excelWorksheet.Cells[row, 2]);
			excelWorksheet.Cells[row++, 2] = others;

			excelWorksheet.Cells[row, 1] = "Average Other Resources:";
			excelWorksheet.Cells[row++, 2] = BuildFormula(CellAddr, CellAddresses.TotalDays, "/"); ;

			excelWorksheet.Cells[row, 1] = "Average Other Resources:";
			if (statisticsPool.TotalHitsNHosts.Hosts > 0)
				excelWorksheet.Cells[row++, 2] = BuildFormula(CellAddr, CellAddresses.TotalHosts, "/");
			else
				excelWorksheet.Cells[row++, 2] = "n/a";

			// set current boundary column number
			if (MaxColumn < 'A' + 1)
				MaxColumn = (char)('A' + 1);
		}


		/// <summary>
		/// Gets vertical offset (in rows) from the given row down on the given height value
		/// </summary>
		/// <param name="excelWorksheet">Worksheet where offset should be calculated</param>
		/// <param name="row">Index of row to calculate offset from</param>
		/// <param name="height">Height of the offset</param>
		/// <returns>Vertical offset in rows</returns>
		static public int GetVertCellOffset(Excel.Worksheet excelWorksheet, int row, double height)
		{
			int res = 0;
			double NeededPos = (double)((excelWorksheet.Cells[row, 1] as Excel.Range).Top) + height;
			while ((double)((excelWorksheet.Cells[row + res, 1] as Excel.Range).Top) < NeededPos)
				res++;

			return res;
		}

		/// <summary>
		/// Gets horizontal offset (in columns) from the given row right on the given width value
		/// </summary>
		/// <param name="excelWorksheet">Worksheet where offset should be calculated</param>
		/// <param name="col">Index of columns to calculate offset from</param>
		/// <param name="width">Width of the offset</param>
		/// <returns>Horizontal offset in columns</returns>
		static public int GetHorzCellOffset(Excel.Worksheet excelWorksheet, int col, double width)
		{
			int res = 0;
			double NeededPos = (double)((excelWorksheet.Cells[1, col] as Excel.Range).Left) + width;
			while (((double)(excelWorksheet.Cells[1, col + res] as Excel.Range).Left) < NeededPos)
				res++;

			return res;
		}

		/// <summary>
		///   Prints change in hits over time.
		/// </summary>
		/// <param name="ws">
		///   Worksheet to output statistics to.
		/// </param>
		/// <param name="row">
		///   Number of the row where output to be started.
		/// </param>
		public void PrintChangeInHitsOverTime(Excel.Worksheet excelWorksheet, ref int row)
		{
			excelWorksheet.Cells[row, 1] = "Change in hits over time";
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			(excelWorksheet.Cells[row++, 1] as Excel.Range).Font.Bold = true;

			int chartrow = ++row;
			double ChartTop1 = (double)(excelWorksheet.Cells[row, 1] as Excel.Range).Top;
			double ChartTop2 = ChartTop1 + ChartHeight;
			int startrow = row = row + GetVertCellOffset(excelWorksheet, row, 2 * ChartHeight);

			SetHeaderCellText(excelWorksheet.Cells[row, 1], "Date");
			SetHeaderCellText(excelWorksheet.Cells[row, 2], "Hits");
			SetHeaderCellText(excelWorksheet.Cells[row, 3], "Hosts");
			row++;

			foreach (KeyValuePair<string, HitsNHosts> kv in statisticsPool.StatList[0] as Dictionary<string, HitsNHosts>)
			{
				DateTime date;
				if (DateTime.TryParse(kv.Key, out date))
					excelWorksheet.Cells[row, 1] = date.Date;

				excelWorksheet.Cells[row, 2] = kv.Value.Hits;
				excelWorksheet.Cells[row++, 3] = kv.Value.Hosts;
			}


			Excel.Shape shape = excelWorksheet.Shapes.AddChart(Excel.XlChartType.xl3DColumnClustered, 1, ChartTop1, ChartWidth, ChartHeight);
			try
			{
				shape.Chart.SetSourceData(excelWorksheet.get_Range(excelWorksheet.Cells[startrow, 1], excelWorksheet.Cells[row - 1, 2]), Excel.XlRowCol.xlColumns);
			}
			finally
			{
				Shapes.Add(shape);
			}
			Excel.Range ChartSource = excelWorksheet.Application.Union(excelWorksheet.get_Range(excelWorksheet.Cells[startrow, 1], excelWorksheet.Cells[row - 1, 1]),
					excelWorksheet.get_Range(excelWorksheet.Cells[startrow, 3], excelWorksheet.Cells[row - 1, 3]), Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing);

			shape = excelWorksheet.Shapes.AddChart(Excel.XlChartType.xl3DColumnClustered, 1, ChartTop2, ChartWidth, ChartHeight);

			try
			{
				shape.Chart.SetSourceData(ChartSource, Excel.XlRowCol.xlColumns);
			}
			finally
			{
				Shapes.Add(shape);
			}

			// set current boundary column number
			if (MaxColumn < 'A' + 2)
				MaxColumn = (char)('A' + 2);
		}


		/// <summary>
		///   Prints hits/hosts statistics for days of week
		/// </summary>
		/// <param name="ws">
		///   Worksheet to output statistics to.
		/// </param>
		/// <param name="row">
		///   Number of the row where output to be started.
		/// </param>
		public void PrintDayOfWeek(Excel.Worksheet excelWorksheet, ref int row)
		{
			Dictionary<string, HitsNHosts> Days = new Dictionary<string, HitsNHosts>();
			DateTime dt = new DateTime(2007, 7, 15);

			string day;

			for (int i = 0; i < 7; i++)
				Days.Add(dt.AddDays(i).DayOfWeek.ToString(), new HitsNHosts(0, 0));

			foreach (KeyValuePair<string, HitsNHosts> kv in statisticsPool.StatList[0] as Dictionary<string, HitsNHosts>)
			{
				try
				{
					day = Convert.ToDateTime(kv.Key).Date.DayOfWeek.ToString();
				}
				catch
				{
					day = "";
				}
				if (Days.ContainsKey(day))
				{
					HitsNHosts t = Days[day];
					t.Hits += kv.Value.Hits;
					t.Hosts += kv.Value.Hosts;
					Days[day] = t;
				}
			}

			excelWorksheet.Cells[row, 1] = "Average Activity by Day of Week";
			(excelWorksheet.Cells[row, 1] as Excel.Range).Font.Underline = true;
			(excelWorksheet.Cells[row++, 1] as Excel.Range).Font.Bold = true;

			int chartrow = ++row;
			double ChartTop1 = (double)(excelWorksheet.Cells[row, 1] as Excel.Range).Top;
			double ChartTop2 = ChartTop1 + ChartHeight;
			int startrow = row = row + GetVertCellOffset(excelWorksheet, row, 2 * ChartHeight);

			SetHeaderCellText(excelWorksheet.Cells[row, 1], "Day of Week");
			SetHeaderCellText(excelWorksheet.Cells[row, 2], "Hits");
			SetHeaderCellText(excelWorksheet.Cells[row, 3], "Hosts");
			row++;

			foreach (KeyValuePair<string, HitsNHosts> kv in Days)
			{
				excelWorksheet.Cells[row, 1] = kv.Key;
				excelWorksheet.Cells[row, 2] = kv.Value.Hits;
				excelWorksheet.Cells[row++, 3] = kv.Value.Hosts;
			}

			Excel.Shape shape = excelWorksheet.Shapes.AddChart(Excel.XlChartType.xl3DColumnClustered, 1, ChartTop1, ChartWidth, ChartHeight);

			try
			{
				shape.Chart.SetSourceData(excelWorksheet.get_Range(excelWorksheet.Cells[startrow, 1], excelWorksheet.Cells[row - 1, 2]), Excel.XlRowCol.xlColumns);
			}
			finally
			{
				Shapes.Add(shape);
			}
			Excel.Range ChartSource = excelWorksheet.Application.Union(excelWorksheet.get_Range(excelWorksheet.Cells[startrow, 1], excelWorksheet.Cells[row - 1, 1]),
					excelWorksheet.get_Range(excelWorksheet.Cells[startrow, 3], excelWorksheet.Cells[row - 1, 3]), Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing);

			shape = excelWorksheet.Shapes.AddChart(Excel.XlChartType.xl3DColumnClustered, 1, ChartTop2, ChartWidth, ChartHeight);

			try
			{
				shape.Chart.SetSourceData(ChartSource, Excel.XlRowCol.xlColumns);
			}
			finally
			{
				Shapes.Add(shape);
			}

			// set current boundary column number
			if (MaxColumn < 'A' + 2)
				MaxColumn = (char)('A' + 2);

		}

		/// <summary>
		///   Fits worksheet into the print area.
		/// </summary>
		/// <param name="ws">
		///   Worksheet to fit.
		/// </param>
		/// <param name="ColNum">
		///   Number of the column to fit.
		/// </param>
		/// <param name="startrow">
		///   Number of the start row of the data to fit.
		/// </param>
		/// /// <param name="endrow">
		///   Number of the last row of the data to fit.
		/// </param>
		private void FitWSToPage(Excel.Worksheet excelWorksheet, int ColNum, int startrow, int endrow)
		{
			// second column of the worksheet contains page URLs, the longest value
			// if page width is more than 82 (A4 page), reduce 2nd column width
			try
			{
				excelWorksheet.Columns.AutoFit();
				double TotalWidth = 0;
				int MaxCol2Width = 82;

				for (int i = 0; i < (MaxColumn - 'A' + 1); i++)
					TotalWidth += Convert.ToDouble((excelWorksheet.Cells[1, i + 1] as Excel.Range).ColumnWidth);

				if (TotalWidth > MaxCol2Width)
				{
					TotalWidth = Convert.ToDouble((excelWorksheet.Cells[1, ColNum] as Excel.Range).ColumnWidth) - TotalWidth + MaxCol2Width;

					if (TotalWidth > 0)
						(excelWorksheet.Cells[1, ColNum] as Excel.Range).ColumnWidth = TotalWidth;

					for (int i = startrow; i < endrow; i++)
						(excelWorksheet.Cells[i, ColNum] as Excel.Range).WrapText = true;
				}

				TotalWidth = 0;

				for (int i = 0; i < (MaxColumn - 'A' + 1); i++)
					TotalWidth += Convert.ToDouble((excelWorksheet.Cells[1, i + 1] as Excel.Range).Width);

				TotalWidth = 420;

				foreach (Excel.Shape shape in Shapes)
					shape.Width = (float)TotalWidth;
				excelWorksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
			}
			finally
			{
				excelWorksheet.Rows.AutoFit();
			}
		}

		/// <summary>
		/// Main function to perform statistics output to the Excel worksheet
		/// </summary>
		/// <param name="ws">Excel worksheet</param>
		/// <returns>True if succeed, false otherwise</returns>
		public string PrintAllToWS(Excel.Worksheet excelWorksheet)
		{
			try
			{
				Shapes.Clear();
				MaxColumn = 'A';

				int row = 1;
				int startrow = row;
				if (PrintGeneralStatistics(excelWorksheet, ref row))
				{
					row++;

					excelWorksheet.HPageBreaks.Add(excelWorksheet.Cells[row, 1]);

					// Entry Pages
					PrintEntryPages(excelWorksheet, ref row);
					excelWorksheet.HPageBreaks.Add(excelWorksheet.Cells[row, 1]);
					row++;

					// exit pages
					PrintExitPages(excelWorksheet, ref row);
					excelWorksheet.HPageBreaks.Add(excelWorksheet.Cells[row, 1]);
					row++;

					// Browsers
					PrintBrowsers(excelWorksheet, ref row);
					excelWorksheet.HPageBreaks.Add(excelWorksheet.Cells[row, 1]);
					row++;

					// Operationg Systems
					PrintOperatingSystems(excelWorksheet, ref row);
					excelWorksheet.HPageBreaks.Add(excelWorksheet.Cells[row, 1]);
					row++;

					// Resources
					PrintResources(excelWorksheet, ref row);
					excelWorksheet.HPageBreaks.Add(excelWorksheet.Cells[row, 1]);
					row++;

					// Change in hits over time
					PrintChangeInHitsOverTime(excelWorksheet, ref row);
					excelWorksheet.HPageBreaks.Add(excelWorksheet.Cells[row, 1]);
					row++;

					// Change in hits over time
					PrintDayOfWeek(excelWorksheet, ref row);
					row++;
				}

				FitWSToPage(excelWorksheet, 2, startrow, row);
				Shapes.Clear();
			}
			catch (Exception ex)
			{
				if (!(ex is System.Threading.ThreadAbortException))
					return "Error: " + ex.Message;
			}

			return "Statistics worksheet complete";
		}

		/// <summary>
		/// Sets text to the cell and formats it bold and with center horizontal alignment
		/// </summary>
		/// <param name="objCell">Cell to assign text and formatting to</param>
		/// <param name="text">Text to assign</param>
		private void SetHeaderCellText(object objCell, string text)
		{
			Excel.Range cell = objCell as Excel.Range;
			cell.Value2 = text;
			cell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
			cell.Font.Bold = true;
		}
	}
}
